﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Globalization;

namespace GPS.Controllers.GPS_DanhMuc
{
    public class GPS_PhuongTienController : FDBController
    {
        #region Unit
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public void initialCategorySearchAction(ViewModelQLPT viewModel)
        {
            string PT_DVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            ViewBag.checkuser = PT_DVGTVT_MA;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(PT_DVGTVT_MA, viewModel.pPT_DVGTVT_MA);
            viewModel.pPT_DVGTVT_MA = getdvgtvt_Ma;

            viewModel.pROW = viewModel.pROW == null ? "50" : viewModel.pROW;
        }
        #endregion

        #region Action
        public ActionResult Index(ViewModelQLPT SearchModel, int? currentPage)
        {
            // check user
            initialCategorySearchAction(SearchModel);
            List<CrudModelQLPT> model = new List<CrudModelQLPT>();
            if (SearchModel.SearchButton == "Xuất Excel")
            {
                try
                {
                    model = GetListData(SearchModel);
                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\DanhSachPhuongTien.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx"));
                    // Using the template to create the newFile...
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        int recordIndex = 7;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.PT_BIENSO.ToString();
                            myWorksheet.Cells[recordIndex, 3].Value = item.PT_NHANHIEU == null ? "" : item.PT_NHANHIEU;
                            myWorksheet.Cells[recordIndex, 4].Value = item.PT_MACXE == null ? "" : item.PT_MACXE.ToString();
                            myWorksheet.Cells[recordIndex, 5].Value = item.PT_NAMSX == null ? "" : item.PT_NAMSX.ToString(); ;
                            myWorksheet.Cells[recordIndex, 6].Value = item.PT_COSOHUU != null ? "" : "X";
                            myWorksheet.Cells[recordIndex, 7].Value = item.PT_COSOHUU == null ? "X" : "";
                            myWorksheet.Cells[recordIndex, 8].Value = item.PT_COPHANHPHU == null ? "X" : "";
                            myWorksheet.Cells[recordIndex, 9].Value = item.PT_COPHANHPHU == null ? "X" : "";
                            myWorksheet.Cells[recordIndex, 10].Value = item.PT_GPXTL != null ? "X" : "";
                            myWorksheet.Cells[recordIndex, 11].Value = item.PT_GPXTL == null ? "X" : "";
                            recordIndex++;
                        }
                        if (SearchModel.pPT_DVGTVT_MA != null)
                        {
                            myWorksheet.Cells[2, 1].Value = "Cơ sở giao thông vận tải" + SearchModel.pPT_DVGTVT_MA == null ? "" : _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == SearchModel.pPT_DVGTVT_MA).DVGTVT_TEN; ;
                        }
                        if (SearchModel.pPT_BIENSO != null)
                        {
                            myWorksheet.Cells[3, 1].Value = "Biển số: " + SearchModel.pPT_BIENSO;
                        }
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, "DanhSachPhuongTien_" + DateTime.Now.ToShortDateString() + ".xlsx");
                }
                catch (Exception ex)
                {
                    TempData["Message"] = ex.ToString();
                    return RedirectToAction("Index");
                }
            }
            else if (SearchModel.SearchButton == "Tìm kiếm")
            {
                if (!string.IsNullOrEmpty(SearchModel.pPT_DVGTVT_MA))
                {
                    model = GetListData(SearchModel);
                }
                ViewBag.TotalRow = model == null ? "0" : model.Count().ToString();
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                return View(SearchModel);
            }
            else
            {
                ViewBag.TotalRow = model == null? "0": model.Count().ToString();
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                return View(SearchModel);
            }
        }
        public ActionResult Detail(string ID)
        {
            CrudModelQLPT model = GetItemData(ID);
            return PartialView(model);
        }
        #endregion

        #region"Ajax"
        public JsonResult AutoCompleteDVGTVT_MA(string term)
        {
            var result = _db.Filter<SDONVIGTVT>(o => o.DVGTVT_MAQL == "40"
                                                && (o.DVGTVT_MA.Contains(term) || o.DVGTVT_TEN.ToUpper().Contains(term.ToUpper()))
                                                && (o.DVGTVT_ACTIVE == "1"));
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        public JsonResult AutoCompleteBIEN_SO(string term, string dvgtvt_ma)
        {
            string dvgtvt_mas = dvgtvt_ma.Split(':')[0];
            var result = _db.Filter<DPT>(o => o.PT_BIENSO.Contains(term) && o.PT_DVGTVT_MA == dvgtvt_mas);
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        #endregion

        #region ExportWord
        public ActionResult ExportWord(string id)
        {
            CrudModelQLPT model = GetItemData(id);
            byte[] contentFile = null;
            contentFile = GPS.ENTITYS.EXPORTWORD_QLPT.EXPORT_QLPT(Server.MapPath("~/Templates/GPS_Word/PhuongTien_ThongTin.docx"), model, "DocxGenericReport");
            string nameOutput = "PhuongTien_" + DateTime.Now.ToShortDateString() + ".docx";
            return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
        }
        #endregion

        #region Getdatabinding
        public List<CrudModelQLPT> GetListData(ViewModelQLPT SearchModel)
        {
            var _pPT_ID = new OracleParameter("pPT_ID", OracleDbType.Int64, string.IsNullOrWhiteSpace(SearchModel.pQD_ID) ? DBNull.Value : (object)SearchModel.pQD_ID, ParameterDirection.Input);
            var _pPT_BIENSO = new OracleParameter("pPT_BIENSO", OracleDbType.Varchar2, (SearchModel.pPT_BIENSO == null ? DBNull.Value : (object)SearchModel.pPT_BIENSO), ParameterDirection.Input);
            var _pPT_SODK = new OracleParameter("pPT_SODK", OracleDbType.Varchar2, (SearchModel.pPT_SODK == null ? DBNull.Value : (object)SearchModel.pPT_SODK), ParameterDirection.Input);
            var _pPT_DVGTVT_MA = new OracleParameter("pPT_DVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pPT_DVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pPT_DVGTVT_MA), ParameterDirection.Input);
            var _pPT_ACTIVE = new OracleParameter("pPT_ACTIVE", OracleDbType.Varchar2, (SearchModel.pPT_ACTIVE == null ? DBNull.Value : (object)SearchModel.pPT_ACTIVE), ParameterDirection.Input);
            var _pROW = new OracleParameter("pROW", OracleDbType.Decimal, (SearchModel.pROW == null ? DBNull.Value : (object)SearchModel.pROW), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<CrudModelQLPT>("BEGIN GPS_HOCVIEN.GPS_GETDS_SEARCH_PHUONGTIEN(:pPT_ID,:pPT_BIENSO,:pPT_SODK,:pPT_DVGTVT_MA,:pPT_ACTIVE,:pROW,:pRET); end;"
                                                                                                                        , _pPT_ID
                                                                                                                        , _pPT_BIENSO
                                                                                                                        , _pPT_SODK
                                                                                                                        , _pPT_DVGTVT_MA
                                                                                                                        , _pPT_ACTIVE
                                                                                                                        , _pROW
                                                                                                                        , _pRET).ToList();
            return entities;
        }
        public CrudModelQLPT GetItemData(string _ID)
        {
            var _pPT_ID = new OracleParameter("pPT_ID", OracleDbType.Decimal, string.IsNullOrWhiteSpace(_ID) ? DBNull.Value : (object)_ID, ParameterDirection.Input);
            var _pPT_BIENSO = new OracleParameter("pPT_BIENSO", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pPT_SODK = new OracleParameter("pPT_SODK", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pPT_DVGTVT_MA = new OracleParameter("pPT_DVGTVT_MA", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pPT_ACTIVE = new OracleParameter("pPT_ACTIVE", OracleDbType.Varchar2, DBNull.Value, ParameterDirection.Input);
            var _pROW = new OracleParameter("pROW", OracleDbType.Decimal, "1", ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<CrudModelQLPT>("BEGIN GPS_HOCVIEN.GPS_GETDS_SEARCH_PHUONGTIEN(:pPT_ID,:pPT_BIENSO,:pPT_SODK,:pPT_DVGTVT_MA,:pPT_ACTIVE,:pROW,:pRET); end;"
                                                                                                                        , _pPT_ID
                                                                                                                        , _pPT_BIENSO
                                                                                                                        , _pPT_SODK
                                                                                                                        , _pPT_DVGTVT_MA
                                                                                                                        , _pPT_ACTIVE
                                                                                                                        , _pROW
                                                                                                                        , _pRET).FirstOrDefault();
            return entities;
        }
        #endregion
    }
}